/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import model.config.MySQLQuery;
import model.pojo.*;
import model.util.MySQLProviders;
/**
 *
 * @author Admin
 */
public class DonDatHangDAO {
    
    public static DonDatHangPOJO getDonDatHang(int MaDonDatHang)
    {
        DonDatHangPOJO dh = new DonDatHangPOJO();
        String sql = "Select * from DonDatHang where MaDonDatHang ='" +MaDonDatHang +"'";
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        ResultSet rs = helper.executeQuery(sql);
        try {
            while(rs.next())
            {
               
                dh.setMaDonDatHang(rs.getInt("MaDonDatHang"));
                dh.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                dh.setNgayDatHang(rs.getTimestamp("NgayDatHang"));
                dh.setSoLuong(rs.getInt("SoLuong"));
                dh.setTongTien(rs.getDouble("TongTien"));
                dh.setNguoiNhan(rs.getString("NguoiNhan"));
                dh.setDiaChi(rs.getString("DiaChi"));
                dh.setDienThoai(rs.getString("DienThoai"));
                dh.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                dh.setMaQuan(rs.getInt("MaQuan"));
                dh.setChuThich(rs.getString("ChuThich"));
                
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return dh;
    }
    
    public static ArrayList<DonDatHangPOJO> getDonDatHang(String TenDangNhap)
    {
        ArrayList<DonDatHangPOJO> ddh = new ArrayList<DonDatHangPOJO>();
        String sql = "Select * from DonDatHang where TenDangNhap ='" +TenDangNhap +"'";
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        ResultSet rs = helper.executeQuery(sql);
        try {
            while(rs.next())
            {
                DonDatHangPOJO temp = new DonDatHangPOJO();
                temp.setMaDonDatHang(rs.getInt("MaDonDatHang"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setNgayDatHang(rs.getTimestamp("NgayDatHang"));
                temp.setSoLuong(rs.getInt("SoLuong"));
                temp.setTongTien(rs.getDouble("TongTien"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setDienThoai(rs.getString("DienThoai"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setChuThich(rs.getString("ChuThich"));
                temp.setTenDangNhap(TenDangNhap);
                
                ddh.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return ddh;
    }
    
    public static ArrayList<DonDatHangPOJO> getDonDatHang2(String TenDangNhap)
    {
        ArrayList<DonDatHangPOJO> ddh = new ArrayList<DonDatHangPOJO>();
        String sql = "Select * from DonDatHang h,TinhTrangDH t where h.MaTinhTrang=t.MaTinhTrang and TenDangNhap ='" +TenDangNhap +"' Order By MaDonDatHang DESC";
        MySQLProviders helper = new MySQLProviders();
        helper.open();
        ResultSet rs = helper.executeQuery(sql);
        try {
            while(rs.next())
            {
                DonDatHangPOJO temp = new DonDatHangPOJO();
                temp.setMaDonDatHang(rs.getInt("MaDonDatHang"));
                temp.setMaTinhTrang(rs.getInt("MaTinhTrang"));
                temp.setNgayDatHang(rs.getTimestamp("NgayDatHang"));
                temp.setSoLuong(rs.getInt("SoLuong"));
                temp.setTongTien(rs.getDouble("TongTien"));
                temp.setNguoiNhan(rs.getString("NguoiNhan"));
                temp.setDiaChi(rs.getString("DiaChi"));
                temp.setDienThoai(rs.getString("DienThoai"));
                temp.setMaKhuVuc(rs.getInt("MaKhuVuc"));
                temp.setMaQuan(rs.getInt("MaQuan"));
                temp.setChuThich(rs.getString("ChuThich"));
                temp.setTenTinhTrang(rs.getString("TenTinhTrang"));
                temp.setTenDangNhap(TenDangNhap);
                
                ddh.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            helper.close();
        }
        return ddh;
    }
    
    public static boolean HuyHoaDon(DonDatHangPOJO hd,Connection con)
    {
        boolean flag = false;
        String sql1 = "UPDATE DonDatHang Set MaTinhTrang = 2  Where MaDonDatHang = "+hd.getMaDonDatHang();
        try
        { 
            Statement statement = con.createStatement(); 
            int n = statement.executeUpdate(sql1);
            if(n == 1)
                System.out.println("Huy hoa don thanh cong ");
            else
                System.out.println("Loi~");               
        }
        catch(SQLException ex)
        { 
            Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return flag;
    }
    
    public static int ThemHoaDon(DonDatHangPOJO hd,Connection con)
    {
        int MaHD = 0;
     
            
            long current_time = System.currentTimeMillis();
            Timestamp NgayHienTai = new Timestamp(current_time);
            hd.setNgayDatHang(NgayHienTai);
            String sql1 = "INSERT INTO DonDatHang (`MaDonDatHang`,`NgayDatHang`,`TongTien`,`TenDangNhap`,`MaTinhTrang`) "
                    + "VALUES(NULL,'"+hd.getNgayDatHang()+"','"+hd.getTongTien()+"','"+hd.getTenDangNhap()+"','"+hd.getMaTinhTrang()+"')";
            try
            { 
                Statement statement = con.createStatement(); 
                int n = statement.executeUpdate(sql1,Statement.RETURN_GENERATED_KEYS);
                if(n == 1)
                    System.out.println("Them hoa don thanh cong ");
                else
                    System.out.println("Loi~");
                ResultSet rs = statement.getGeneratedKeys();
                if(rs.next())
                    MaHD = rs.getInt(1);
            }
            catch(SQLException ex)
            { 
                Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
                
            }


        return MaHD;
    }
    
    public static int ThemHoaDon2(DonDatHangPOJO hd,Connection con)
    {
        int MaHD = 0;
     
            
            long current_time = System.currentTimeMillis();
            Timestamp NgayHienTai = new Timestamp(current_time);
            hd.setNgayDatHang(NgayHienTai);
            String sql1 = "INSERT INTO DonDatHang (`MaDonDatHang`,`NgayDatHang`,`TongTien`,`TenDangNhap`,"
                    + "`MaTinhTrang`,`ChuThich`,`HinhThucThanhToan`,`PhuongThucChuyenHang`,"
                    + "`NguoiNhan`,`MaKhuVuc`,`MaQuan`,`DiaChi`,`DienThoai`) "
                    + "VALUES(NULL,'"+hd.getNgayDatHang()+"','"+hd.getTongTien()+"','"+hd.getTenDangNhap()+"',"
                    + "'"+hd.getMaTinhTrang()+"','"+hd.getChuThich()+"','"+hd.getHinhThucThanhToan()+"',"
                    + "'"+hd.getPhuongThucChuyenHang()+"','"+hd.getNguoiNhan()+"',"
                    + "'"+hd.getMaKhuVuc()+"','"+hd.getMaQuan()+"',"
                    + "'"+hd.getDiaChi()+"','"+hd.getDienThoai()+"')";
            try
            { 
                Statement statement = con.createStatement(); 
                int n = statement.executeUpdate(sql1,Statement.RETURN_GENERATED_KEYS);
                if(n == 1)
                    System.out.println("Them hoa don thanh cong ");
                else
                    System.out.println("Loi~");
                ResultSet rs = statement.getGeneratedKeys();
                if(rs.next())
                    MaHD = rs.getInt(1);
            }
            catch(SQLException ex)
            { 
                Logger.getLogger(DonDatHangDAO.class.getName()).log(Level.SEVERE, null, ex);
                
            }


        return MaHD;
    }
    
    public static ArrayList<DonDatHangPOJO> getDanhSachCoPhanTrang(int trang) throws FileNotFoundException, IOException {
      ArrayList<DonDatHangPOJO> ds = new ArrayList<DonDatHangPOJO>(); 
      int soSanPhamTrenTrang = ThamSoDAO.laySoLuongTrenTrang();
      int batDau = (trang - 1) * soSanPhamTrenTrang;
      if(soSanPhamTrenTrang==batDau)
          batDau=1;     
      //mo ket noi
      MySQLProviders helper = new MySQLProviders();
      helper.open();
      
      //doc query tu file text
      //lay cau truy van tu file text
     MySQLQuery mySQLQuery=new MySQLQuery();
     mySQLQuery.Read("PHANTRANG");
     String sql=String.format(mySQLQuery.GetQuery(MySQLQuery.QueryKey.SELECT_ALL_DONDATHANG_PHANTRANG),batDau,soSanPhamTrenTrang);
      ResultSet rs = helper.executeQuery(sql);
      try {
         while (rs.next()) {
            DonDatHangPOJO temp = new DonDatHangPOJO();
            temp.setMaDonDatHang(rs.getInt("MaDonDatHang"));
            temp.setMaTinhTrang(rs.getInt("MaTinhTranh"));
            temp.setNgayDatHang(rs.getTimestamp("NgayDatHang"));
            temp.setSoLuong(rs.getInt("SoLuong"));
            temp.setTongTien(rs.getDouble("TongTien"));  
            temp.setTenDangNhap(rs.getString("TenDangNhap"));
            ds.add(temp);
         }
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      helper.close();
      return ds;
   }
    
    public static DonDatHangPOJO getById(int id){
       DonDatHangPOJO obj=new DonDatHangPOJO();
       MySQLProviders helper = new MySQLProviders();
       helper.open();
       ResultSet rs = helper.executeQuery("select *from dondathang where madondathang="+id);
       try {
           while (rs.next()) {            
            obj.setMaDonDatHang(rs.getInt("MaDonDatHang"));
            obj.setMaTinhTrang(rs.getInt("MaTinhTranh"));
            obj.setNgayDatHang(rs.getTimestamp("NgayDatHang"));
            obj.setSoLuong(rs.getInt("SoLuong"));
            obj.setTongTien(rs.getDouble("TongTien"));  
            obj.setTenDangNhap(rs.getString("TenDangNhap"));
            
         }
       }catch(Exception e){
           
       }finally{
           helper.close();
       }
       return obj;
   }
}
